﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Threading;
using DataTable = System.Data.DataTable;

namespace WinformMDB
{
    public partial class Form1 : Form
    {
        static string path = "D:\\Code\\测井数据库_套管节箍深度表.mdb";
        static string tablename = "AJCJ12";
        static string row = "top 107";
        static string sqlComm = "Select " + row + " * From " + tablename;
        static string topFolderPath = @"D:\Code\WinformMDB";
        //static int rowNumPerPage = 20;
        /*----暂时不需要
        //int rowNum = 0;
        //string colName = "";
        //int startRowNum = 0;
        //int endRowNum = 0;
        */
        //----查询及页码相关
        int maxPageNum;
        int currentPageNum;
        //----类
        MDBConn mDBConn = new MDBConn(path);
        DataSet dataPool = new DataSet();
        DataTable da = new DataTable();
        DataTable datb = new DataTable();
        List<string> wellNumList = new List<string>();
        static Semaphore sem = new Semaphore(1, 1);

        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// RESIZE调整单元格大小按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click_1(object sender, EventArgs e)
        {
            this.dataGridView1.AutoResizeColumns();
            this.dataGridView1.AutoResizeRows();
            this.dataGridView1.AutoResizeColumnHeadersHeight();
        }

        /// <summary>
        /// 关键：READ读取按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ReadFile_Click_1(object sender, EventArgs e)
        {
            //初始化数据
            this.currentPageNum = 1;
            //读取文件并显示
            try
            {
                mDBConn.Open();
                wellNumList = GetJHColumn();
                this.datb = Kernel(wellNumList[currentPageNum-1], true);
            }   
            catch (Exception readErr)
            {
                throw new Exception("An Error occur :" + readErr.Message);
                //mDBConn = null;
            }
            //最大页码就是井号数量
            this.maxPageNum = wellNumList.Count();
            //显示数据,路径,页码。
            this.dataGridView1.DataSource = datb;
            this.FilePath.Text = path;
            this.curPage.Text = currentPageNum.ToString() + "/" + maxPageNum.ToString();
            //启用按钮
            this.button1.Enabled = true;
            this.button2.Enabled = true;
            this.button3.Enabled = true;
            this.NextPage.Enabled = true;
            this.LastPage.Enabled = true;

        }

        /// <summary>
        /// SAVE当前JH页保存按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            
            try
            {
                SaveAsExcel(this.datb, topFolderPath);
            }
            catch (Exception err)
            {
                throw new Exception("Save Failure:" + err.Message);
            }

        }

        /// <summary>
        /// 上一页按钮点击
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void LastPage_Click(object sender, EventArgs e)
        {
            //上一页
            if (this.currentPageNum <= 1)
                WarnPageOperation(false);
            else
            {
                NormalPageOperation(false);
            }
        }

        /// <summary>
        /// 下一页按钮点击
        /// </summary>
        /// <param name="sender">按钮本身</param>
        /// <param name="e"></param>
        private void NextPage_Click(object sender, EventArgs e)
        {
            //下一页
            if (this.currentPageNum >= this.maxPageNum)
                WarnPageOperation(true);
            else
            {
                NormalPageOperation(true);
            }
        }

        /// <summary>
        /// SQL查询的核心操作
        /// </summary>
        /// <param name="start">想要获取的起始行</param>
        /// <returns>返回从起始行接下来一页的行</returns>
        public DataTable Kernel(string wellNumber, bool func)
        {
            OleDbCommand command = new OleDbCommand();
            DataTable tempDT = new DataTable();
            //构建SQL
            string sql;
            //Select * From AJCJ12 Where JH = "0016A" Order By JSXH
            sql = string.Format("Select * From {0} Where JH = \"{1}\" Order By JHDM, JSXH;"
                ,tablename, wellNumber);

            //写入语句
            if (func)
            {
                command = mDBConn.WriteInSQL(sql);
                tempDT = mDBConn.GetDataSetByReader(command);
            }
            else
            {
                tempDT = mDBConn.GetDataSetByAdapter(sql);
            }
            
            //this.textBox1.Text = sql;
            return tempDT;
        }

        /// <summary>
        /// 查询表格行数
        /// </summary>
        /// <returns>最大行数</returns>
        public int QueryRowCounts()
        {
            //查询表格行数
            int rows = 0;
            string queryRows = "select count(*) From " + tablename;
            OleDbCommand queRows = new OleDbCommand();
            queRows = mDBConn.WriteInSQL(queryRows);
            rows = (int)queRows.ExecuteScalar();
            
            return rows;
        }

        /// <summary>
        /// 获取井号列
        /// </summary>
        /// <returns>井号数组</returns>
        public List<string> GetJHColumn()
        {   //局部变量定义
            List<string> jh = new List<string>();
            string querySql = "Select Distinct JH From " + tablename;
            OleDbCommand queCol = new OleDbCommand();
            //写入并读取
            queCol = mDBConn.WriteInSQL(querySql);
            OleDbDataReader jhReader = queCol.ExecuteReader();

            while(jhReader.Read())
            {
                jh.Add(jhReader[0].ToString().Trim());
            }
            jhReader.Close();
            return jh;
        }

        /// <summary>
        /// 前后翻页的基本读取并显示
        /// </summary>
        /// <param name="lastORnext">翻页操作</param>
        /// <returns>直接写入框体中</returns>
        public void NormalPageOperation(bool lastORnext)
        {
            if (lastORnext)//next TRUE last FALSE
            {
                this.currentPageNum += 1;
                datb = Kernel(wellNumList[currentPageNum - 1],true);
                this.dataGridView1.DataSource = datb;
                this.FilePath.Text = path;
                this.curPage.Text = currentPageNum.ToString() + "/" + maxPageNum.ToString();
            }
            else
            {
                this.currentPageNum -= 1;
                datb = Kernel(wellNumList[currentPageNum - 1],true);
                this.dataGridView1.DataSource = datb;
                this.FilePath.Text = path;
                this.curPage.Text = currentPageNum.ToString() + " / " + maxPageNum.ToString();
            }
        }

        /// <summary>
        /// 前后翻页警示
        /// </summary>
        /// <param name="opt">前后翻页bool</param>
        /// <returns>写入文本框，不返回</returns>
        public void WarnPageOperation(bool LastOrNext)
        {
            if (LastOrNext)
            {
                string warring = "无法向后翻页。";
                this.FilePath.Text = warring;
            }
            else
            {
                string warring = "无法向前翻页。";
                this.FilePath.Text = warring;
            }
        }

        /// <summary>
        /// 将DataTable保存为Excel
        /// </summary>
        /// <param name="curPage">数据表</param>
        /// <param name="topFolderPath">保存目录</param>
        public void SaveAsExcel(DataTable curPage, string topFolderPath)
        {
            string wellName = curPage.Rows[1][1].ToString().Trim();
            //string topFolderPath = @"D:\Code\WinformMDB";
            string folderPath = System.IO.Path.Combine(topFolderPath, wellName);
            string fileName = wellName + ".xlsx";
            string filePath = System.IO.Path.Combine(folderPath, fileName);

            System.IO.Directory.CreateDirectory(folderPath);
            //this.textBox2.Text = filePath;
            ExcelOperation.SuperToExcel(curPage, filePath);
        }
        
        /// <summary>
        /// 将DataTable保存到DataSet中
        /// </summary>
        public void LoopLoad()
        {
            DataTable temp = new DataTable();
            
            foreach (string well in wellNumList)
            {
                sem.WaitOne();
                temp = Kernel(well,false);
                dataPool.Tables.Add(temp);
                sem.Release();
            }
            
        }

        public DataTable LoadAll()
        {
            DataTable all = new DataTable();
            string getAll = "Select * From AJCJ12 Order By JHDM, JSXH;";
            all = mDBConn.GetDataSetByAdapter(getAll);
            return all;
        }

        /// <summary>
        /// 将DataSet中的表一个个保存为Excel
        /// </summary>
        public void LoopSave()
        {
            DataTable savingTable = new DataTable();
            string saveTopPath = @"D:\Code\WinformMDB\Excel\";
            Thread.Sleep(1000);
            for (int i=0; i<wellNumList.Count();i++)
            {
                sem.WaitOne();
                //if (this.dataPool.Tables[i] == null)
                //{
                    //Thread.Sleep(1000);
                    //continue;
                //}

                savingTable = this.dataPool.Tables[i];
                SaveAsExcel(savingTable, saveTopPath);
                sem.Release();
            }
            
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //禁用按钮
            this.button3.Enabled = false;
            //局部变量声明
            int now = 0;
            double progress = 0;
            //进度条弹窗
            Form2 proBarWindow = new Form2();
            proBarWindow.Show();
            proBarWindow.InitProgressBar(0, 100);
            
            //尝试双线程
            Thread LoadThread = new Thread(new ThreadStart(LoopLoad));
            Thread SaveThread = new Thread(new ThreadStart(LoopSave));
            LoadThread.Priority = ThreadPriority.AboveNormal;
            SaveThread.Priority = ThreadPriority.BelowNormal;
            LoadThread.Start();
            SaveThread.Start();
            

            
        }
    }
}
